package com.sg.hms.DaoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;

import com.sg.hms.Dao.MoneyApplicationDao;
import com.sg.hms.Model.Applicationer;
import com.sg.hms.Util.JDBCUtil;
/**
 * 
 * @author lubinghan
 *
 */
public class MoneyApplicationDaoImpl implements MoneyApplicationDao{

	/**
	 * 查询当前月各部门支出的数据总数
	 */
	@Override
	public int selectMoneyByNowAndSate() {
		// TODO Auto-generated method stub
		int count = 0;
		StringBuffer sql = new StringBuffer("select count(maId) as total from moneyapplication where date_format(approveDate,'%Y-%m')=date_format(now(),'%Y-%m') and state = '已通过'");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			ResultSet set = pst.executeQuery();
			if (set.next()) {
				count = set.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	
	/**
	 * 实现各部门消费的查询
	 */
	@Override
	public JSONObject selectMoneyByPage(int pageStart, int pageSize, int total) {
		// TODO Auto-generated method stub
		JSONObject spends = new JSONObject();
		spends.put("total", total);
		List<Applicationer> applicationers = new ArrayList<Applicationer>();
		StringBuffer sql = new StringBuffer("select dName,aContent,maId,money,approveDate from moneyapplication ,application,employee,department where  moneyapplication.aId = application.aId and application.elId = employee.elId and employee.dId = department.dId and moneyapplication.state = '已通过' and date_format(approveDate,'%Y-%m')=date_format(now(),'%Y-%m')  limit ?,?");
		
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setInt(1, pageStart);
			pst.setInt(2, pageSize);
			ResultSet set = pst.executeQuery();
			while (set.next()) {
				Applicationer applicationer = new Applicationer();
				applicationer.setdName(set.getString("dName"));
				applicationer.setMaId(set.getInt("maId"));
				applicationer.setMoney(set.getInt("money"));
				applicationer.setApproveDate(set.getString("approveDate"));
				applicationer.setaContent(set.getString("aContent"));
				applicationers.add(applicationer);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		spends.put("rows", applicationers);
		return spends;
	}
	
	//根据日期查询某月的部门申请总支出
	@Override
	public List<Applicationer> selectTotalMoneyByDate(int pageStart,
			int pageSize,String date) {
		// TODO Auto-generated method stub
		List<Applicationer> applicationers = new ArrayList<Applicationer>();
		StringBuffer sql = new StringBuffer("select dName,sum(money ) as sum");
		sql.append(" from moneyapplication ,application,employee,department ");
		sql.append(" where  moneyapplication.aId = application.aId ");
		sql.append(" and application.elId = employee.elId ");
		sql.append(" and employee.dId = department.dId ");
		sql.append(" and moneyapplication.state = '已通过' ");
		sql.append(" and date_format(approveDate, '%Y%m')= ?  ");
		sql.append(" group by dName  limit ?,?");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			pst.setInt(2, pageStart);
			pst.setInt(3, pageSize);
			ResultSet set = pst.executeQuery();
			while (set.next()) {
				Applicationer applicationer = new Applicationer();
				applicationer.setdName(set.getString("dName"));
				applicationer.setMoney(set.getInt("sum"));
				applicationers.add(applicationer);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return applicationers;
	}

	@Override
	public List<Applicationer> selectMoneyByDate(int pageStart, int pageSize, String date) {
		// TODO Auto-generated method stub
		List<Applicationer> applicationers = new ArrayList<Applicationer>();
		StringBuffer sql = new StringBuffer("select dName,aContent,maId,money,approveDate ");
		sql.append(" from moneyapplication ,application,employee,department ");
		sql.append(" where  moneyapplication.aId = application.aId ");
		sql.append(" and application.elId = employee.elId ");
		sql.append(" and employee.dId = department.dId  ");
		sql.append(" and moneyapplication.state = '已通过' ");
		sql.append(" and date_format(approveDate, '%Y%m')= ?   ");
		sql.append("  limit ?,?");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			pst.setInt(2, pageStart);
			pst.setInt(3, pageSize);
			ResultSet set = pst.executeQuery();
			while (set.next()) {
				Applicationer applicationer = new Applicationer();
				applicationer.setdName(set.getString("dName"));
				applicationer.setaContent(set.getString("aContent"));
				applicationer.setMoney(set.getInt("money"));
				applicationer.setApproveDate(set.getString("approveDate"));
				applicationers.add(applicationer);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return applicationers;
	}
	/**
	 *  根据日期查询某月的各部门申请批准的消费数据条数
	 */
	@Override
	public int selectMoneyOfDataCountByDate(String date) {
		// TODO Auto-generated method stub
		int count = 0;
		StringBuffer sql = new StringBuffer("select count(maId) as total  ");
		sql.append(" from moneyapplication");
		sql.append(" where date_format(approveDate, '%Y%m')= ? ");
		sql.append(" and state = '已通过'");
		Connection conn = JDBCUtil.getConnection();
		try {
			PreparedStatement pst = conn.prepareStatement(sql.toString());
			pst.setString(1, date);
			ResultSet set = pst.executeQuery();
			if (set.next()) {
				count = set.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	
}
